package com.example.flshtarvel.mapper;

import com.example.flshtarvel.entity.RouteTagMap;
import com.example.flshtarvel.entity.Tag;
import com.example.flshtarvel.vo.RouteVo;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface RouteTagMapMapper {
    @Insert("insert into route_tag_map(route_id,tag_id) values(#{routeId},#{tagId})")
    public int insertRouteTagMap(String routeId,String tagId);

    @Delete("delete from route_tag_map where route_id=#{routeId}")
    public int deleteRouteTagMApByRouteId(String routeId);

    //@Select("select * from route_tag_map,route where route_tag_map.route_id=route.route_id and route_tag_map.tag_id=#{tagId}")
    @Select("select route.*,likeView.like_count,collectView.collect_count  from route,route_tag_map, (select route.route_id,count(love.message_id) as like_count from route  LEFT JOIN love on route.route_id = love.message_id GROUP BY(route.route_id)) likeView, (select route.route_id,count(collect.message_id) as collect_count  from route  LEFT JOIN collect on route.route_id = collect.message_id GROUP BY(route.route_id)) collectView where route.route_id = likeView.route_id and route.route_id = collectView.route_id and route_tag_map.route_id=route.route_id and route_tag_map.tag_id=#{tagId}")
    public List<RouteVo> selectRouteTagMapByTagId(String tagId);

    @Select("select tag.* from route_tag_map,tag where route_tag_map.tag_id = tag.tag_id and route_tag_map.route_id = #{routeId}")
    public List<Tag> selectRouteTagMapByRouteId(String routeId);


}

